package com.yitong.xjd.dao;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.yitong.xjd.domain.CustCreditRecord;
import com.yitong.xjd.domain.CustSalLoanScoreInfo;
import com.yitong.xjd.domain.XjdParams;
import com.yitong.xjd.domain.ZxcxData;

@Repository
public class XinJinDaiDaoImpl implements XinJinDaiDao {
	
	@Autowired
	@Qualifier("secondJdbcTemplate")
    private JdbcTemplate jdbcTemplate;
	
	//获取本地征信记录
	@Override
	public CustCreditRecord getZxjlFromLocal(String custidtno) {
		String sql="select cust_idt_no custIdtNo,"
				+ "cust_age custAge,"
				+ "cust_marr custMarr,"
				+ "cust_endu custEndu,"
				+ "cust_titl custTitl,"
				+ "cust_regist_add custRegistAdd,"
				+ "cust_query_cnt custQueryCnt,"
				+ "cust_credit custCredit,"
				+ "cust_valid_credit custValidCredit,"
				+ "cust_long_bal custLongBal,"
				+ "query_date queryDate,"
				+ "invalid_date invalidDate,"
				+ "input_date inputDate,"
				+ "yuqi_flag yuqiFlag, "
				+ "time_out_back timeOutBack "
				+ "from crm_fact.cust_credit_record A "
				+ "where A.cust_idt_no=? "
				+ "and sysdate < (to_date(A.invalid_date,'yyyy-mm-dd')+1) "
				+ "and A.time_out_back='0'";
		
		List<CustCreditRecord> list = jdbcTemplate.query(sql, new Object[]{custidtno}, new RowMapper<CustCreditRecord>(){
			@Override
			public CustCreditRecord mapRow(ResultSet rs, int rowNum)throws SQLException {
				CustCreditRecord t = new CustCreditRecord();
				t.setCustIdtNo(rs.getString("custIdtNo"));
				t.setCustAge(rs.getString("custAge"));
				t.setCustMarr(rs.getString("custMarr"));
				t.setCustEndu(rs.getString("custEndu"));
				t.setCustTitl(rs.getString("custTitl"));
				t.setCustRegistAdd(rs.getString("custRegistAdd"));
				t.setCustQueryCnt(rs.getString("custQueryCnt"));
				t.setCustCredit(rs.getString("custCredit"));
				t.setCustValidCredit(rs.getString("custValidCredit"));
				t.setCustLongBal(rs.getString("custLongBal"));
				t.setQueryDate(rs.getString("queryDate"));
				t.setInvalidDate(rs.getString("invalidDate"));
				t.setInputDate(rs.getString("inputDate"));
				t.setYuqiFlag(rs.getString("yuqiFlag"));
				t.setTimeOutBack(rs.getString("timeOutBack"));
				return t;
			}});
		if(list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}
	
	//获取薪金贷积分
	@Override
	public double getScoreByEntity(CustSalLoanScoreInfo record,String custNo,boolean flag){
		List<XjdParams> list=this.getDaiKuanParams();
		double score=this.getScore(record,list,custNo,flag);
		return score;
	}
	
	//获取参数信息
	public List<XjdParams> getDaiKuanParams(){
		String sql="select par_name,par_par_id,par_value,par_score,par_weight from crm_fact.cust_sal_loan_score_para where par_value is not null";
		List<XjdParams> list = jdbcTemplate.query(sql, new Object[]{}, new RowMapper<XjdParams>(){
			@Override
			public XjdParams mapRow(ResultSet rs, int rowNum)throws SQLException {
				XjdParams t = new XjdParams();
				t.setPar_name(rs.getString("par_name"));
				t.setPar_par_id(rs.getString("par_par_id"));
				t.setPar_value(rs.getString("par_value"));
				t.setPar_score(rs.getDouble("par_score"));
				t.setPar_weight(rs.getDouble("par_weight"));
				return t;
			}});
		return list;
	}
	
	//计算积分
	private double getScore(CustSalLoanScoreInfo record, List<XjdParams> list, String custNo, boolean flag) {
		List<String> loglist=new ArrayList<String>();
		double c0=this.getItemScore("C0",record.getCustAge(),list,loglist);
		double c1=this.getItemScore("C1",record.getCustMarr(),list,loglist);
		double c2=this.getItemScore("C2",record.getCustEndu(),list,loglist);
		double c3=this.getItemScore("C3",record.getCustTitl(),list,loglist);
		double c5=this.getItemScore("C5",record.getCustRegistAdd(),list,loglist);
		double c6=this.getItemScore("C6",record.getCustQueryCnt(),list,loglist);
		double c7=this.getItemScore("C7",record.getCustCredit(),list,loglist);
		double c8=this.getItemScore("C8",record.getCustValidCredit(),list,loglist);
		double c9=this.getItemScore("C9",record.getCustLongBal(),list,loglist);
		double c10=this.getItemScore("C10",record.getCustProxyTimes(),list,loglist);
		double c11=this.getItemScore("C11",record.getCustMonthSalaryFlag(),list,loglist);
		double c12=this.getItemScore("C12",record.getCustLvl(),list,loglist);
		double c13 = this.getItemScore("C13",count(record.getCustLoyal()),list,loglist);
		double c14=this.getItemScore("C14",record.getUnitRiskRank(),list,loglist);
		double sum=c0+c1+c2+c3+c5+c6+c7+c8+c9+c10+c11+c12+c13+c14;
		System.out.println("TotalScore = "+sum);
		//记录日志
		String strs="";
		for(String str:loglist){
			strs=strs.concat(str).concat("\n");
		}
		this.insertCalLog(custNo,record.getCustIdtNo(),"1",strs);
		//返回
		return sum;
	}
	
	//计算客户忠诚度
	private String count(String custLoyal) {
		int n=0;
		if(custLoyal!=null&&!custLoyal.equals("")){
			for(int i=0;i<custLoyal.length();i++){
				String s=custLoyal.substring(i, i+1);
				if(s.equals("1")){
					n=n+1;
				}
			}
			if(n==0){
				return "1";	
			}else{
				return n+"";
			}
		}else{
			return "1";
		}
	}
	
	//计算每个字段的分数
	private double getItemScore(String colsflag, String value, List<XjdParams> list,List<String> loglist) {
		for(XjdParams r:list){
			if(r.getPar_par_id().equals(colsflag)&&r.getPar_value().equals(value)){
				String str=colsflag+" = "+r.getPar_score()+" x "+r.getPar_weight()+" <--"+r.getPar_name();
				loglist.add(str);
				return r.getPar_score()*r.getPar_weight();
			}
		}
		return 0;
	}
	
	//插入日志
	public Serializable insertCalLog(final String custNo, final String custIdtNo, final String flag, final String strs) {
		String sql="insert into crm_fact.cust_credit_record_log(cust_no,cust_idt_no,flag,input_date,log_desc) values(?,?,?,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),?)";
		int c=jdbcTemplate.update(sql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, custNo);
				ps.setString(2, custIdtNo);
				ps.setString(3, flag);
				ps.setString(4,strs);
			}});
		return c;
	}
	
	//插入征信接口查来的数据
	@Override
	public Serializable insertRecord(final CustCreditRecord record) {
		String sql="insert into crm_fact.cust_credit_record(cust_idt_no,cust_age,cust_marr,cust_endu,cust_titl,cust_regist_add,cust_query_cnt,cust_credit,cust_valid_credit,cust_long_bal,query_date,invalid_date,input_date,yuqi_flag,time_out_back) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		int c=jdbcTemplate.update(sql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, record.getCustIdtNo());
				ps.setString(2, record.getCustAge());
				ps.setString(3, record.getCustMarr());
				ps.setString(4, record.getCustEndu());
				ps.setString(5, record.getCustTitl());
				ps.setString(6, record.getCustRegistAdd());
				ps.setString(7, record.getCustQueryCnt());
				ps.setString(8, record.getCustCredit());
				ps.setString(9, record.getCustValidCredit());
				ps.setString(10, record.getCustLongBal());
				ps.setString(11, record.getQueryDate());
				ps.setString(12, record.getInvalidDate());
				ps.setString(13, record.getInputDate());
				ps.setString(14, record.getYuqiFlag());
				ps.setString(15, record.getTimeOutBack());
			}});
		return c;
	}
	
	//根据身份证获取本单位工作年龄
	@Override
	public String queryCustWorkAge(String custIdtNo) {
		String sql=	"select round((A.unit_proxy_times/A.month_proxy_times)/12,0) from crm_dm.per_proxy_salary_index A,cust_info B where A.CUST_ID=B.Cust_Id_Merge and A.data_date=(to_char(trunc(sysdate,'MM')-1,'yyyymmdd')) and B.cust_idt_no=? and B.cust_mpng_flag in ('0','1') ";
		BigDecimal c=jdbcTemplate.queryForObject(sql, new Object[]{custIdtNo}, java.math.BigDecimal.class);
		if(c==null){
			return null;
		}else{
			return c.toString();
		}
	}

	//根据身份证获取平均工资
	@Override
	public String queryCustMonthBal(String custIdtNo) {
		String sql=	"select cust_month_salary from crm_fact.cust_sal_loan_score_info where cust_idt_no=?";
		BigDecimal c=jdbcTemplate.queryForObject(sql, new Object[]{custIdtNo}, java.math.BigDecimal.class);
		if(c==null){
			return null;
		}else{
			return c.toString();
		}
	}
	
	//更新本地信息
	@Override
	public int updateEntity(final CustSalLoanScoreInfo entity) {
		String sql="update cust_sal_loan_score_info A "
				+ "set A.cust_age=?,"
				+ "A.cust_marr=?,"
				+ "A.cust_endu=?,"
				+ "A.cust_titl=?,"
				+ "A.cust_regist_add=?, "
				+ "A.cust_query_cnt=?,"
				+ "A.cust_credit=?,"
				+ "A.cust_valid_credit=?,"
				+ "A.cust_long_bal=? "
				+ "where A.cust_idt_no=?";
		int c=jdbcTemplate.update(sql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, entity.getCustAge());
				ps.setString(2, entity.getCustMarr());
				ps.setString(3, entity.getCustEndu());
				ps.setString(4,entity.getCustTitl());
				ps.setString(5, entity.getCustRegistAdd());
				ps.setString(6, entity.getCustQueryCnt());
				ps.setString(7,entity.getCustCredit());
				ps.setString(8, entity.getCustValidCredit());
				ps.setString(9, entity.getCustLongBal());
				ps.setString(10, entity.getCustIdtNo());
			}});
		return c;
	}

	//身份证号查询数据记录
	@Override
	public CustSalLoanScoreInfo getBeanByCustIdtNo(String custidtno) {
		String sql="select A.cust_id custId,"
				+ "A.cust_name custName,"
				+ "A.cust_gend custGend,"
				+ "A.cust_idt custIdt,"
				+ "A.cust_idt_no custIdtNo,"
				+ "A.cust_lvl custLvl, "
				+ "A.cust_age custAge,"
				+ "A.cust_marr custMarr,"
				+ "A.cust_endu custEndu,"
				+ "A.cust_titl custTitl,"
				+ "A.cust_regist_add custRegistAdd, "
				+ "A.cust_query_cnt custQueryCnt,"
				+ "A.cust_credit custCredit,"
				+ "A.cust_valid_credit custValidCredit,"
				+ "A.cust_long_bal custLongBal,"
				+ "A.cust_proxy_months custProxyMonths, "
				+ "nvl(A.cust_month_salary,0) custMonthSalary,"
				+ "A.cust_month_salary_flag custMonthSalaryFlag,"
				+ "A.cust_loyal custLoyal,"
				+ "A.unit_risk_rank unitRiskRank,"
				+ "A.data_date dataDate， "
				+ "A.unit_order unitOrder, "
				+ "A.yavg_salary yavgSalary, "
				+ "A.unit_proxy_times unitProxyTimes "
				+ "from cust_sal_loan_score_info A "
				+ "where A.cust_idt_no=? ";
		List<CustSalLoanScoreInfo> list = jdbcTemplate.query(sql, new Object[]{custidtno}, new RowMapper<CustSalLoanScoreInfo>(){
			@Override
			public CustSalLoanScoreInfo mapRow(ResultSet rs, int rowNum)throws SQLException {
				CustSalLoanScoreInfo t = new CustSalLoanScoreInfo();
				t.setCustId(rs.getBigDecimal("custId"));
				t.setCustName(rs.getString("custName"));
				t.setCustGend(rs.getString("custGend"));
				t.setCustIdt(rs.getString("custIdt")); 
				t.setCustIdtNo(rs.getString("custIdtNo"));
				t.setCustLvl(rs.getString("custLvl"));
				t.setCustAge(rs.getString("custAge"));
				t.setCustMarr(rs.getString("custMarr"));
				t.setCustEndu(rs.getString("custEndu"));
				t.setCustTitl(rs.getString("custTitl"));
				t.setCustRegistAdd(rs.getString("custRegistAdd"));
				t.setCustQueryCnt(rs.getString("custQueryCnt"));
				t.setCustCredit(rs.getString("custCredit"));
				t.setCustValidCredit(rs.getString("custValidCredit"));
				t.setCustLongBal(rs.getString("custLongBal"));
				t.setCustProxyMonths(rs.getString("custProxyMonths"));
				t.setCustMonthSalary(rs.getDouble("custMonthSalary"));
				t.setCustMonthSalaryFlag(rs.getString("custMonthSalaryFlag"));
				t.setCustLoyal(rs.getString("custLoyal"));
				t.setUnitRiskRank(rs.getString("unitRiskRank"));
				t.setDataDate(rs.getString("dataDate"));
				t.setUnitOrder(rs.getString("unitOrder"));
				t.setYavgSalary(rs.getString("yavgSalary"));
				t.setUnitProxyTimes(rs.getString("unitProxyTimes"));
				return t;
			}
		});
		
		if(list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}
	
	//临时保存核心号
	@Override
	public Serializable insertBakCustNo(final String custidtno, final String custno,final String product,final String status) {
		String sql="insert into crm_fact.cust_credit_record_mid(cust_no,cust_idt_no,product_code,status,input_date) values(?,?,?,?,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'))";
		int c=jdbcTemplate.update(sql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, custno);
				ps.setString(2, custidtno);
				ps.setString(3, product);
				ps.setString(4,status);
			}});
		return c;
	}
	
	//更新核心号
	@Override
	public Serializable updateBakCustNo(final String custIdtNo,final String status) {
		String sql="update crm_fact.cust_credit_record_mid set status=?,update_date=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') where cust_idt_no=? and status='1'";
		int c=jdbcTemplate.update(sql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, status);
				ps.setString(2, custIdtNo);
			}});
		return c;
	}
	
	//检查是否在征信查询中
	@Override
	public boolean isExistInBak(String custIdtNo,String status) {
		String sql="select count(*) from crm_fact.cust_credit_record_mid where cust_idt_no=? and status=?";
		int n = jdbcTemplate.queryForObject(sql,new Object[]{custIdtNo,status},java.lang.Integer.class); 
		if(n>0){
			return true;
		}else{
			return false;
		}
	}

	//获取征信查询中的记录
	@Override
	public ZxcxData getBakBean(String custidtno) {
		String sql="select cust_no,cust_idt_no,status,product_code,input_date,update_date from crm_fact.cust_credit_record_mid where cust_idt_no='"+custidtno+"' and status='1'";
		List<ZxcxData> list = jdbcTemplate.query(sql, new Object[]{}, new RowMapper<ZxcxData>(){
			@Override
			public ZxcxData mapRow(ResultSet rs, int rowNum)throws SQLException {
				ZxcxData t = new ZxcxData();
				t.setCustNo(rs.getString("cust_no"));
				t.setCustIdtNo(rs.getString("cust_idt_no"));
				t.setStatus(rs.getString("status"));
				t.setProductCode(rs.getString("product_code"));
				t.setInputDate(rs.getString("input_date"));
				t.setUpdateDate(rs.getString("update_date"));
				return t;
			}});
		if(list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}

	//获取信贷机构
	@Override
	public String getOrg(String custidtno) {
		String xdjg="-1";
		try {
			String sql="select orgid from ind_info@lc A "
					+ "where certid = ? "
					+ "and certtype='0' "
					+ "and exists ("
					+ "select 1 from business_duebill@lc B where a.customerid=b.customerid union all "
					+ "select 1 from business_apply@lc C where a.customerid=c.customerid union all " 
					+ "select 1 from business_contract@lc D where a.customerid=d.customerid"
					+ ")"; 
			List<String> list1 = jdbcTemplate.queryForList(sql, new Object[]{custidtno}, java.lang.String.class);
			if(list1.size()>0){
				if(list1.size()>1){
					System.out.println("信贷信息表中查出异常条数数据(>1):"+list1.size());
				}else{
					String v=(String) list1.get(0);
					if(v!=null&&!v.equals("")){
						String sql1="select A.mainframeorgid from org_info@lc A where orgid like ? and orglevel='9' and rownum=1 order by orgId ";
						List<String> list = jdbcTemplate.queryForList(sql1, new Object[]{v+"%"}, java.lang.String.class);
						if(list.size()>0){
							Object c = list.get(0);
							xdjg = c.toString();
						}else{
							System.out.println("信贷机构号转换为普通机构号失败");
						}
					}else{
						System.out.println("此人信贷信息表中机构号为空");
					}
				}
			}else{
				System.out.println("信贷信息表中查出异常条数数据(0):"+list1.size());
				xdjg = "000";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xdjg;
	}

	//查询白名单
	@Override
	public String getFromWhitList(String custidtno) {
		String sql=	"select ent_lvl from crm_fact.per_credit_record_whitelist where cust_idt_no=?";
		List<String> list = jdbcTemplate.queryForList(sql, new Object[]{custidtno}, String.class);
		if(list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}

	//征信查询队列表插入
	@Override
	public Serializable insertQueryZxcxData(final ZxcxData node) {
		String sql="insert into crm_fact.cust_credit_record_queue(cust_no,cust_idt_no,product_code,status,input_date) values(?,?,?,'0',to_char(sysdate,'yyyy-MM-dd hh24:mi:ss'))";
		int c=jdbcTemplate.update(sql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, node.getCustNo());
				ps.setString(2, node.getCustIdtNo());
				ps.setString(3, node.getProductCode());
			}});
		return c;
	}

	@Override
	public List<ZxcxData> getAllQueryData() {
		String sql="select cust_no,cust_idt_no,product_code,status,input_date,update_date from crm_fact.cust_credit_record_queue";
		List<ZxcxData> list = jdbcTemplate.query(sql, new Object[]{}, new RowMapper<ZxcxData>(){
			@Override
			public ZxcxData mapRow(ResultSet rs, int rowNum)throws SQLException {
				ZxcxData t = new ZxcxData();
				t.setCustNo(rs.getString("cust_no"));
				t.setCustIdtNo(rs.getString("cust_idt_no"));
				t.setStatus(rs.getString("status"));
				t.setProductCode(rs.getString("product_code"));
				t.setInputDate(rs.getString("input_date"));
				t.setUpdateDate(rs.getString("update_date"));
				return t;
			}});
		return list;
	}

	@Override
	public int removeZxcxData(final ZxcxData node) {
		String sql="delete from crm_fact.cust_credit_record_queue where cust_idt_no=?";
		int c=jdbcTemplate.update(sql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, node.getCustIdtNo());
			}});
		return c;
	}

	@Override
	public int getQueryQueueSize() {
		String sql=	"select count(1) from crm_fact.cust_credit_record_queue";
		BigDecimal c=jdbcTemplate.queryForObject(sql, new Object[]{}, java.math.BigDecimal.class);
		if(c==null){
			return 0;
		}else{
			return c.intValue();
		}
	}

	@Override
	public String getNowTime(String format) {
		String sql=	"select to_char(sysdate,?) from dual";
		String c=jdbcTemplate.queryForObject(sql, new Object[]{format}, String.class);
		return c;
	}

	@Override
	public String getCustName(String custno, String custidtno) {
		String sql="select cust_name from cust_info where cust_no=?";
		List<String> list = jdbcTemplate.queryForList(sql, new Object[]{custno}, String.class);
		if(list.size()>1||list.size()<1){
			return null;
		}else{
			return (String)list.get(0);
		}
	}

	@Override
	public List<ZxcxData> getYestDayQueryData() {
		String sql="select "
				+ "A.cust_no,"
				+ "A.cust_idt_no,"
				+ "A.product_code,"
				+ "A.status,"
				+ "A.input_date,"
				+ "A.update_date,"
				+ "(select cust_name from cust_info where cust_no= A.cust_no) custName "
				+ "from crm_fact.cust_credit_record_mid A "
				+ "where status='2' "
				+ "and update_date is not null "
				+ "and input_date like to_char(sysdate-1,'YYYY-MM-DD')||'%'";
		
		List<ZxcxData> list = jdbcTemplate.query(sql, new Object[]{}, new RowMapper<ZxcxData>(){
			@Override
			public ZxcxData mapRow(ResultSet rs, int rowNum)throws SQLException {
				ZxcxData t = new ZxcxData();
				t.setCustNo(rs.getString("cust_no"));
				t.setCustIdtNo(rs.getString("cust_idt_no"));
				t.setStatus(rs.getString("status"));
				t.setProductCode(rs.getString("product_code"));
				t.setInputDate(rs.getString("input_date"));
				t.setUpdateDate(rs.getString("update_date"));
				t.setCustName(rs.getString("custName"));
				return t;
			}});
		return list;
	}

	@Override
	public List<ZxcxData> getOneHourQueryData() {
		String sql="select "
				+ "A.cust_no,"
				+ "A.cust_idt_no,"
				+ "A.product_code,"
				+ "A.status,"
				+ "A.input_date,"
				+ "A.update_date,"
				+ "(select cust_name from cust_info where cust_no= A.cust_no) custName "
				+ "from crm_fact.cust_credit_record_queue A";
		
		List<ZxcxData> list = jdbcTemplate.query(sql, new Object[]{}, new RowMapper<ZxcxData>(){
			@Override
			public ZxcxData mapRow(ResultSet rs, int rowNum)throws SQLException {
				ZxcxData t = new ZxcxData();
				t.setCustNo(rs.getString("cust_no"));
				t.setCustIdtNo(rs.getString("cust_idt_no"));
				t.setStatus(rs.getString("status"));
				t.setProductCode(rs.getString("product_code"));
				t.setInputDate(rs.getString("input_date"));
				t.setUpdateDate(rs.getString("update_date"));
				t.setCustName(rs.getString("custName"));
				return t;
			}});
		return list;
	}
}
